<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MySQL操作</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        .container {
            max-width: 1200px;
            margin-top: 30px;
        }
        .card {
            margin-bottom: 20px;
            box-shadow: 0 4px 8px rgba(0,0,0,0.1);
        }
        .sql-editor {
            min-height: 150px;
            font-family: monospace;
            resize: vertical;
        }
        .table-responsive {
            max-height: 400px;
            overflow-y: auto;
        }
        .alert {
            margin-top: 20px;
            display: none;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1 class="text-center mb-4">MySQL数据库操作</h1>
        
        <div class="row mb-4">
            <div class="col-md-6">
                <div class="card">
                    <div class="card-header bg-primary text-white">
                        <h5 class="mb-0">数据库管理</h5>
                    </div>
                    <div class="card-body">
                        <div class="mb-3">
                            <label for="dbName" class="form-label">数据库名称</label>
                            <div class="input-group">
                                <input type="text" class="form-control" id="dbName" placeholder="输入数据库名称">
                                <button class="btn btn-success" type="button" id="createDb">创建数据库</button>
                                <button class="btn btn-danger" type="button" id="dropDb">删除数据库</button>
                            </div>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">数据库列表</label>
                            <div class="input-group">
                                <select class="form-select" id="dbList">
                                    <option value="">-- 选择数据库 --</option>
                                </select>
                                <button class="btn btn-primary" type="button" id="useDb">使用数据库</button>
                                <button class="btn btn-info" type="button" id="refreshDbList">刷新</button>
                            </div>
                        </div>
                        <div class="alert alert-info" id="currentDbInfo">
                            当前数据库: <span id="currentDb">未选择</span>
                        </div>
                    </div>
                </div>
            </div>
            
            <div class="col-md-6">
                <div class="card">
                    <div class="card-header bg-primary text-white">
                        <h5 class="mb-0">表管理</h5>
                    </div>
                    <div class="card-body">
                        <div class="mb-3">
                            <label class="form-label">表列表</label>
                            <div class="input-group">
                                <select class="form-select" id="tableList">
                                    <option value="">-- 选择表 --</option>
                                </select>
                                <button class="btn btn-danger" type="button" id="dropTable">删除表</button>
                                <button class="btn btn-info" type="button" id="refreshTableList">刷新</button>
                            </div>
                        </div>
                        <div class="mb-3">
                            <button class="btn btn-success" type="button" id="showTableData">查看表数据</button>
                            <button class="btn btn-primary" type="button" id="showTableStructure">查看表结构</button>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        
        <div class="card">
            <div class="card-header bg-primary text-white">
                <h5 class="mb-0">SQL执行</h5>
            </div>
            <div class="card-body">
                <div class="mb-3">
                    <label for="sqlEditor" class="form-label">SQL语句</label>
                    <textarea class="form-control sql-editor" id="sqlEditor" rows="5" placeholder="输入SQL语句，例如: SELECT * FROM student"></textarea>
                </div>
                <div class="mb-3">
                    <button type="button" class="btn btn-primary" id="executeSQL">执行SQL</button>
                    <button type="button" class="btn btn-secondary" id="clearSQL">清空</button>
                    <button type="button" class="btn btn-info ms-2" id="loadStudent">加载Student表创建SQL</button>
                </div>
                <div class="alert alert-success" id="successAlert">操作成功！</div>
                <div class="alert alert-danger" id="errorAlert">操作失败！</div>
            </div>
        </div>
        
        <div class="card">
            <div class="card-header bg-primary text-white">
                <h5 class="mb-0">查询结果</h5>
            </div>
            <div class="card-body">
                <div class="mb-3" id="resultInfo"></div>
                <div class="table-responsive">
                    <table class="table table-striped table-bordered" id="resultTable">
                        <thead id="resultTableHead"></thead>
                        <tbody id="resultTableBody"></tbody>
                    </table>
                </div>
            </div>
        </div>
        
        <div class="d-flex justify-content-between mt-4">
            <a href="/static/index.html" class="btn btn-secondary">返回首页</a>
            <a href="/static/mysql_config.html" class="btn btn-primary">数据库配置页面</a>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            // 获取当前数据库
            const getCurrentDb = () => {
                fetch('/api/module/mysql/current-db')
                    .then(response => response.json())
                    .then(data => {
                        if (data.success && data.data) {
                            document.getElementById('currentDb').textContent = data.data;
                            document.getElementById('currentDbInfo').style.display = 'block';
                        } else {
                            document.getElementById('currentDb').textContent = '未选择';
                        }
                    })
                    .catch(error => {
                        console.error('获取当前数据库失败:', error);
                    });
            };
            
            // 加载数据库列表
            const loadDatabases = () => {
                fetch('/api/module/mysql/databases')
                    .then(response => response.json())
                    .then(data => {
                        if (data.success) {
                            const dbList = document.getElementById('dbList');
                            dbList.innerHTML = '<option value="">-- 选择数据库 --</option>';
                            
                            data.data.forEach(db => {
                                const option = document.createElement('option');
                                option.value = db;
                                option.textContent = db;
                                dbList.appendChild(option);
                            });
                        } else {
                            showAlert('errorAlert', '获取数据库列表失败：' + data.message);
                        }
                    })
                    .catch(error => {
                        showAlert('errorAlert', '获取数据库列表请求失败');
                        console.error('获取数据库列表失败:', error);
                    });
            };
            
            // 加载表列表
            const loadTables = () => {
                fetch('/api/module/mysql/tables')
                    .then(response => response.json())
                    .then(data => {
                        if (data.success) {
                            const tableList = document.getElementById('tableList');
                            tableList.innerHTML = '<option value="">-- 选择表 --</option>';
                            
                            data.data.forEach(table => {
                                const option = document.createElement('option');
                                option.value = table;
                                option.textContent = table;
                                tableList.appendChild(option);
                            });
                        } else {
                            showAlert('errorAlert', '获取表列表失败：' + data.message);
                        }
                    })
                    .catch(error => {
                        showAlert('errorAlert', '获取表列表请求失败');
                        console.error('获取表列表失败:', error);
                    });
            };
            
            // 创建数据库
            document.getElementById('createDb').addEventListener('click', function() {
                const dbName = document.getElementById('dbName').value.trim();
                if (!dbName) {
                    showAlert('errorAlert', '请输入数据库名称');
                    return;
                }
                
                fetch('/api/module/mysql/execute', {
                    method: 'POST',
                    headers: {
                        'Content-Type': 'application/json'
                    },
                    body: JSON.stringify({
                        sql: `CREATE DATABASE IF NOT EXISTS ${dbName}`
                    })
                })
                .then(response => response.json())
                .then(data => {
                    if (data.success) {
                        showAlert('successAlert', `数据库 ${dbName} 创建成功`);
                        loadDatabases();
                    } else {
                        showAlert('errorAlert', '创建数据库失败：' + data.message);
                    }
                })
                .catch(error => {
                    showAlert('errorAlert', '创建数据库请求失败');
                    console.error('创建数据库失败:', error);
                });
            });
            
            // 删除数据库
            document.getElementById('dropDb').addEventListener('click', function() {
                const dbName = document.getElementById('dbName').value.trim();
                if (!dbName) {
                    showAlert('errorAlert', '请输入数据库名称');
                    return;
                }
                
                if (!confirm(`确定要删除数据库 ${dbName} 吗？此操作不可恢复！`)) {
                    return;
                }
                
                fetch('/api/module/mysql/execute', {
                    method: 'POST',
                    headers: {
                        'Content-Type': 'application/json'
                    },
                    body: JSON.stringify({
                        sql: `DROP DATABASE IF EXISTS ${dbName}`
                    })
                })
                .then(response => response.json())
                .then(data => {
                    if (data.success) {
                        showAlert('successAlert', `数据库 ${dbName} 删除成功`);
                        loadDatabases();
                        getCurrentDb();
                    } else {
                        showAlert('errorAlert', '删除数据库失败：' + data.message);
                    }
                })
                .catch(error => {
                    showAlert('errorAlert', '删除数据库请求失败');
                    console.error('删除数据库失败:', error);
                });
            });
            
            // 使用数据库
            document.getElementById('useDb').addEventListener('click', function() {
                const dbName = document.getElementById('dbList').value;
                if (!dbName) {
                    showAlert('errorAlert', '请选择数据库');
                    return;
                }
                
                fetch('/api/module/mysql/use-database', {
                    method: 'POST',
                    headers: {
                        'Content-Type': 'application/json'
                    },
                    body: JSON.stringify({
                        database: dbName
                    })
                })
                .then(response => response.json())
                .then(data => {
                    if (data.success) {
                        showAlert('successAlert', `已切换到数据库 ${dbName}`);
                        document.getElementById('currentDb').textContent = dbName;
                        document.getElementById('currentDbInfo').style.display = 'block';
                        loadTables();
                    } else {
                        showAlert('errorAlert', '切换数据库失败：' + data.message);
                    }
                })
                .catch(error => {
                    showAlert('errorAlert', '切换数据库请求失败');
                    console.error('切换数据库失败:', error);
                });
            });
            
            // 刷新数据库列表
            document.getElementById('refreshDbList').addEventListener('click', function() {
                loadDatabases();
            });
            
            // 刷新表列表
            document.getElementById('refreshTableList').addEventListener('click', function() {
                loadTables();
            });
            
            // 删除表
            document.getElementById('dropTable').addEventListener('click', function() {
                const tableName = document.getElementById('tableList').value;
                if (!tableName) {
                    showAlert('errorAlert', '请选择表');
                    return;
                }
                
                if (!confirm(`确定要删除表 ${tableName} 吗？此操作不可恢复！`)) {
                    return;
                }
                
                fetch('/api/module/mysql/execute', {
                    method: 'POST',
                    headers: {
                        'Content-Type': 'application/json'
                    },
                    body: JSON.stringify({
                        sql: `DROP TABLE IF EXISTS ${tableName}`
                    })
                })
                .then(response => response.json())
                .then(data => {
                    if (data.success) {
                        showAlert('successAlert', `表 ${tableName} 删除成功`);
                        loadTables();
                    } else {
                        showAlert('errorAlert', '删除表失败：' + data.message);
                    }
                })
                .catch(error => {
                    showAlert('errorAlert', '删除表请求失败');
                    console.error('删除表失败:', error);
                });
            });
            
            // 查看表数据
            document.getElementById('showTableData').addEventListener('click', function() {
                const tableName = document.getElementById('tableList').value;
                if (!tableName) {
                    showAlert('errorAlert', '请选择表');
                    return;
                }
                
                document.getElementById('sqlEditor').value = `SELECT * FROM ${tableName}`;
                document.getElementById('executeSQL').click();
            });
            
            // 查看表结构
            document.getElementById('showTableStructure').addEventListener('click', function() {
                const tableName = document.getElementById('tableList').value;
                if (!tableName) {
                    showAlert('errorAlert', '请选择表');
                    return;
                }
                
                document.getElementById('sqlEditor').value = `DESCRIBE ${tableName}`;
                document.getElementById('executeSQL').click();
            });
            
            // 执行SQL
            document.getElementById('executeSQL').addEventListener('click', function() {
                const sql = document.getElementById('sqlEditor').value.trim();
                if (!sql) {
                    showAlert('errorAlert', '请输入SQL语句');
                    return;
                }
                
                fetch('/api/module/mysql/execute', {
                    method: 'POST',
                    headers: {
                        'Content-Type': 'application/json'
                    },
                    body: JSON.stringify({
                        sql: sql
                    })
                })
                .then(response => response.json())
                .then(data => {
                    if (data.success) {
                        showAlert('successAlert', 'SQL执行成功');
                        
                        // 显示结果信息
                        const resultInfo = document.getElementById('resultInfo');
                        if (data.isQuery) {
                            resultInfo.textContent = `查询返回 ${data.data.length} 条记录`;
                            displayQueryResult(data.data);
                        } else {
                            resultInfo.textContent = `操作成功，影响 ${data.affectedRows} 行`;
                            clearResultTable();
                        }
                        
                        // 如果是CREATE TABLE或DROP TABLE，刷新表列表
                        if (sql.toUpperCase().includes('CREATE TABLE') || sql.toUpperCase().includes('DROP TABLE')) {
                            loadTables();
                        }
                        
                        // 如果是USE语句，更新当前数据库
                        if (sql.toUpperCase().startsWith('USE ')) {
                            getCurrentDb();
                            loadTables();
                        }
                    } else {
                        showAlert('errorAlert', 'SQL执行失败：' + data.message);
                        clearResultTable();
                    }
                })
                .catch(error => {
                    showAlert('errorAlert', 'SQL执行请求失败');
                    console.error('SQL执行失败:', error);
                    clearResultTable();
                });
            });
            
            // 清空SQL
            document.getElementById('clearSQL').addEventListener('click', function() {
                document.getElementById('sqlEditor').value = '';
            });
            
            // 加载Student表创建SQL
            document.getElementById('loadStudent').addEventListener('click', function() {
                document.getElementById('sqlEditor').value = `CREATE TABLE student(
    id INT(8),
    name VARCHAR(20),
    gender VARCHAR(1),
    age INT(2)
)`;
            });
            
            // 显示查询结果
            function displayQueryResult(data) {
                if (!data || data.length === 0) {
                    clearResultTable();
                    return;
                }
                
                const tableHead = document.getElementById('resultTableHead');
                const tableBody = document.getElementById('resultTableBody');
                
                // 清空表头和表体
                tableHead.innerHTML = '';
                tableBody.innerHTML = '';
                
                // 创建表头
                const headerRow = document.createElement('tr');
                const columns = Object.keys(data[0]);
                
                columns.forEach(column => {
                    const th = document.createElement('th');
                    th.textContent = column;
                    headerRow.appendChild(th);
                });
                
                tableHead.appendChild(headerRow);
                
                // 创建表体
                data.forEach(row => {
                    const tr = document.createElement('tr');
                    
                    columns.forEach(column => {
                        const td = document.createElement('td');
                        td.textContent = row[column] !== null ? row[column] : 'NULL';
                        tr.appendChild(td);
                    });
                    
                    tableBody.appendChild(tr);
                });
            }
            
            // 清空结果表
            function clearResultTable() {
                document.getElementById('resultTableHead').innerHTML = '';
                document.getElementById('resultTableBody').innerHTML = '';
                document.getElementById('resultInfo').textContent = '';
            }
            
            // 显示提示信息
            function showAlert(elementId, message) {
                const alertElement = document.getElementById(elementId);
                alertElement.textContent = message;
                alertElement.style.display = 'block';
                
                // 3秒后自动隐藏
                setTimeout(() => {
                    alertElement.style.display = 'none';
                }, 3000);
            }
            
            // 初始化
            getCurrentDb();
            loadDatabases();
            loadTables();
        });
    </script>
</body>
</html> 